# -*- coding: utf-8 -*-

import sqlite3
import os

db_terminator = '.llv'
class lilidb:
    def __init__(self):
        self.file_name = ''
        self.con = None
    
    def create_empty_db( self, destination ):
        if destination != ':memory:':
            self.file_name = destination + db_terminator
            if os.path.exists( self.file_name ):
                raise FileAlreadyExists()

        self.con = sqlite3.connect( self.file_name )

        cur = con.cursor()

        # author table
        cur.execute('''create table author(name TEXT,
    birthday TEXT, deathday TEXT, description TEXT, PRIMARY KEY( name, birthday))
    ''')

        # book table
        cur.execute('''CREATE TABLE book(
                       title TEXT, release_year TEXT,
                       read_date TEXT, grade INTEGER,
                       edition INTEGER, language TEXT,
                       description TEXT, publisher_name TEXT,
                       PRIMARY KEY( title, release_year, edition),
                       FOREIGN KEY( publisher_name ) REFERENCES publisher(name) )''')

        # write table
        cur.execute('''CREATE TABLE write(
                       author_name TEXT, birthday TEXT,
                       title TEXT, release_year TEXT, edition TEXT,
                       PRIMARY KEY( author_name, birthday, title, release_year, edition ),
                       FOREIGN KEY( author_name ) REFERENCES author(name),
                       FOREIGN KEY( birthday ) REFERENCES author(birthday),
                       FOREIGN KEY( title ) REFERENCES book(title),
                       FOREIGN KEY( release_year ) REFERENCES book(release_year),
                       FOREIGN KEY( edition ) REFERENCES book(edition) )''')

        # publisher table
        cur.execute('''CREATE TABLE publisher(
                       name TEXT, country TEXT,
                       PRIMARY KEY( name ))''')

        self.con.commit()

    def new_author( self, author_name, birthday,
                    deathday, description ):
        # TODO
        pass

    def fill_database(self):
        cur = self.con.cursor()

        for t in [('diogo','25/09/1984',None , 'descrição teste'),
                  ('aline','07/06/1983',None , 'mais uma descrição teste')]:
            cur.execute('INSERT INTO author VALUES (?,?,?,?)',t)


    def get_authors( self, author ):
        cur = self.con.cursor()
        return cur.execute('SELECT * from author')


    def insert_author( self, author_tuple ):
        cur = self.con.cursor()
        try:
            cur.execute('INSERT INTO author VALUES (?,?,?,?)', author_tuple)
        except:
            return False

        return True


    def get_author_by_name( self, author ):
        cur = self.con.cursor()
        return cur.execute('SELECT * FROM author WHERE name=?',author)


    def close_db( self ):
        self.con.close()
